package com.turing.pre.processor.file.processor;


import com.alibaba.fastjson.JSONArray;
import com.turing.common.entity.TextVO;
import com.turing.common.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.cglib.proxy.Enhancer;
import org.springframework.cglib.proxy.MethodInterceptor;
import org.springframework.cglib.proxy.MethodProxy;

import java.io.*;
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.util.*;


public class ExcelParser {

    private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);

    public static class WorkbookFactory{

        @SuppressWarnings("all")
        private static HashMap<String,Class> typeMap = new HashMap<String,Class>(){
            {
                put("xls",HSSFWorkbook.class);
                put("xlsx",XSSFWorkbook.class);
            }
        };

        @SuppressWarnings("all")
        public static Workbook getWorkBook(InputStream inputStream,String type) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException {

            Class clazz = typeMap.get(type);
            Constructor constructor = clazz.getConstructor(InputStream.class);
            return (Workbook)constructor.newInstance(inputStream);

        }

    }

    public static List<TextVO> doParse(String excelPath,String type){

        List<TextVO> result = new ArrayList<>();
        try(InputStream inputStream = new FileInputStream(excelPath)){
            Workbook workbook = WorkbookFactory.getWorkBook(inputStream,type);
            int numberOfSheets = workbook.getNumberOfSheets();
            int pageNo = 0;
            int pageStartIndex = 0;
            int pageEndIndex = 0;
            for (int i = 0; i < numberOfSheets; i++) {

                Sheet sheet = workbook.getSheetAt(i);
                int rows=sheet.getPhysicalNumberOfRows();
                if(rows == 0 || sheet.getRow(0) == null){
                    continue;
                }
                TextVO textVO = new TextVO();
                textVO.setPageNo(pageNo);
                textVO.setDocID("v1_"+ StringUtils.genRandomNum());
                textVO.setPageStartIndex(pageStartIndex);
                Map<String, TextVO.Line> contIndex = new HashMap<>();
                List<TextVO.Paragraph> paragraphs = new ArrayList<>();
                TextVO.Paragraph paragraph = new TextVO.Paragraph();
                List<com.turing.common.entity.TextVO.Line> lines = new ArrayList<>();
                pageNo++;

                int excelCells=sheet.getRow(0).getPhysicalNumberOfCells();
                for(int r=0;r<rows;r++){
                    TextVO.Line line = new TextVO.Line();
                    List<TextVO.Cell> cells =  new ArrayList<>();
                    line.setObjType("text");
                    line.setObjPos(JSONArray.parseArray("[0,0,0,0]"));
                    line.setLineStartIndex(pageStartIndex);
                    //HSSFRow row=sheet.getRow(r);
                    Row row = sheet.getRow(r);

                    for(int c=0;c<excelCells;c++){
                        TextVO.Cell cell = new TextVO.Cell();
                        cell.setMerge(isMergedRegion(sheet,r,c));

                        cell.setMerge(false);
                        String objContent = "";
                        if(isMergedRegion(sheet,r,c)){
                            populateMergedRegionValue(sheet, r, c,cell,type);
                        }else{
                            if(row.getCell(c) != null){
                                objContent = String.valueOf(getCellValue(row.getCell(c)));
                            }
                            cell.setObjContent(objContent);
                        }

                        pageStartIndex += objContent.length();
                        pageEndIndex += objContent.length();
                        cells.add(cell);
                    }

                    line.setLineNo(r);
                    line.setLineId("line_" + pageNo + "_" + r + "_" + StringUtils.genRandomNum());
                    line.setLineEndIndex(pageEndIndex);
                    line.setCells(cells);
                    lines.add(line);
                }
                paragraph.setParaId("0");
                paragraph.setParaNo("0");
                paragraph.setLines(lines);
                paragraphs.add(paragraph);
                textVO.setParagraphs(paragraphs);
                textVO.setContIndex(contIndex);
                textVO.setPageEndIndex(pageEndIndex);
                result.add(textVO);
            }
        } catch (IOException | NoSuchMethodException | IllegalAccessException | InvocationTargetException | InstantiationException e) {
            e.printStackTrace();
        }
        return result;

    }


    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    private static  boolean isMergedRegion(Sheet sheet, int row , int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static void populateMergedRegionValue( Sheet sheet, int row, int column, TextVO.Cell cell, String excelType) {

        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    cell.setObjContent(getCellValue(fCell));
                    TextVO.MergeInfo mergeInfo = new TextVO.MergeInfo();
                    mergeInfo.setFirstColumn(firstColumn);
                    mergeInfo.setLastColumn(lastColumn);
                    mergeInfo.setFirstLine(firstRow);
                    mergeInfo.setLastLine(lastRow);
                    cell.setMerge(true);
                    cell.setMergeInfo(mergeInfo);
                    return;
                }
            }
        }
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (null != cell) {
            switch (cell.getCellTypeEnum()) {
                case FORMULA:
                case NUMERIC: // 数字
                    DecimalFormat df = new DecimalFormat("0");//处理科学计数法
                    return df.format(cell.getNumericCellValue());
                case STRING: // 字符串
                    return cell.getStringCellValue() + "";
                case BOOLEAN: // Boolean
                    return cell.getBooleanCellValue() + "";
                case BLANK: // 空值
                    return "";
                case ERROR: // 故障
                    return "";
                default:
                    return "未知类型   ";
            }
        } else {
            return "";
        }

    }

    public static void main(String[] args) {

        List<TextVO> textVOS = doParse("D:\\project\\11.xls","xls");
        System.out.println(textVOS.toString());

    }

}
